-- @owner: lihongji
-- @date: 2022/07/28
-- @testpoint: case when测试

--step1:建序列;expect:成功
drop sequence if exists sq_ustore_regr_intercept_0018;
create sequence sq_ustore_regr_intercept_0018 increment by 1 start with 10;

--step2:建表并插入数据;expect:成功
drop table if exists t_ustore_regr_intercept_0018 cascade;
create table t_ustore_regr_intercept_0018(
 col_1 integer,
 col_2 number(19,0),
 col_3 number(10,6)
) with (storage_type=ustore);

begin
 for i in 1..50 loop
  insert into t_ustore_regr_intercept_0018 values(
  i,
  sq_ustore_regr_intercept_0018.nextval,
  i+445.255
  );
 end loop;
end;
/
--step3:查询;expect:成功
select case when regr_intercept(case when col_1>10 then col_1 else col_2 end,case when col_1>10 then col_1 else col_2 end)>149 then regr_intercept(case when col_2>10 then col_2 else col_1 end,case when col_2>10 then col_2 else col_1 end) else variance(case when col_2>50 then col_2 else col_3 end) end from t_ustore_regr_intercept_0018 order by 1;
select regr_intercept(case when col_2>10 then col_2 else col_1 end,case when col_2>10 then col_2 else col_1 end) from t_ustore_regr_intercept_0018 order by 1;

--step4:清理环境;expect:成功
drop sequence if exists sq_ustore_regr_intercept_0018;
drop table if exists t_ustore_regr_intercept_0018 cascade;